package pg

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"math"
	"strconv"
	"strings"
	"match-spider/util/prop"
	"fmt"
)

var dbmap *sqlx.DB

func initDb() (*sqlx.DB,error) {
	if dbmap != nil {
		return dbmap,nil
	}
	//connStr := "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full"
	connStr := "postgres://" + prop.Get("db", "db.username") + ":" + prop.Get("db", "db.password") + "@" + prop.Get("db", "db.url")

	dbmap, err := sqlx.Open("postgres", connStr)
	if err != nil {
		return nil,err
	}
	dbmap.SetMaxOpenConns(2000)
	dbmap.SetMaxIdleConns(1000)
	dbmap.Ping()

	return dbmap,nil
}

func Insert(sql string, params ...interface{}) int64 {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return int64(-3)
	}
	defer dbmap.Close()
	stmtIns, err := dbmap.Prepare(sql)

	if err != nil {
		fmt.Println("pgsql insert error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return int64(-2)
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

	ret, err := stmtIns.Exec(params...)
	if err != nil {

		if strings.Contains(err.Error(), "duplicate") {
			fmt.Println("duplicate====>")
			return int64(0)
		}
		fmt.Println("pgsql insert error：%s,sql：%s,params：%+v",err.Error(),sql,params)

		return int64(-1)
	}
	id, err := ret.LastInsertId()
	if err != nil {
		return int64(0)
	}
	return id
}
func UpdateOrDelete(sql string, params ...interface{}) bool {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return false
	}
	defer dbmap.Close()
	stmtIns, err := dbmap.Prepare(sql)

	if err != nil {
		fmt.Println("pgsql update or delete error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return false
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
	ret, err := stmtIns.Exec(params...)
	if err != nil {
		fmt.Println("pgsql update or delete error：%s,sql：%s,params：%+v",err.Error(),sql,params)

		return false
	}
	_, err = ret.RowsAffected()
	if err != nil {
		fmt.Println("pgsql update or delete error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return false
	}

	return true
}
func FindFirst(sql string, params ...interface{}) map[string]interface{} {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return nil
	}
	defer dbmap.Close()
	sql = sql + " limit 1"
	rows, err := dbmap.Query(sql, params...)

	if err != nil {
		fmt.Println("pgsql FindFirst error：%s,sql：%s,params：%+v",err.Error(),sql,params)

		return nil
	}
	defer rows.Close()

	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	record := make(map[string]interface{})
	for i := range values {
		scanArgs[i] = &values[i]
	}
	var len = 0
	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		if err != nil {

			fmt.Println("pgsql FindFirst error：%s,sql：%s,params：%+v",err.Error(),sql,params)
			return nil
		}
		for i, col := range values {
			if col != nil {
				switch col.(type) {
				case []byte:
					record[columns[i]] = string(col.([]uint8))
				default:
					record[columns[i]] = col
				}
			}
		}
		len++
		break
	}
	if len > 0 {
		return record
	}
	return nil
}
func Count(sql string, params ...interface{}) int64 {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return int64(0)
	}
	defer dbmap.Close()
	sql = sql + " limit 1"
	rows, err := dbmap.Query(sql, params...)
	defer rows.Close()

	if err != nil {

		fmt.Println("pgsql count error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return 0
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))
	var count = "0"
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)

		for _, col := range values {
			if col != nil {
				count = string(col)
				break
			}
		}
		break
	}
	num, _ := strconv.ParseInt(count, 10, 64)
	return num
}
func FindAll(sql string, params ...interface{}) []map[string]interface{} {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return nil
	}
	defer dbmap.Close()

	rows, err := dbmap.Query(sql, params...)
	defer rows.Close()

	if err != nil {

		fmt.Println("pgsql FindAll error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return nil
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))
	records := []map[string]interface{}{}
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record := make(map[string]interface{})
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col)
			}
		}
		records = append(records, record)
	}
	return records
}

func Paginate(page int64, length int64, query string, sqlExceptSelect string, params ...interface{}) map[string]interface{} {
	dbmap,err := initDb()
	if err!=nil{
		fmt.Println("pgsql initDb error：%s",err.Error())
		return nil
	}
	defer dbmap.Close()

	data := make(map[string]interface{})
	sqlExceptSelectCount:=sqlExceptSelect
	ss:=strings.Replace(sqlExceptSelect," ","",-1)
	if strings.Contains(ss,"orderby"){
		sqlExceptSelectCount=strings.Split(sqlExceptSelectCount,"order by")[0]
	}
	queryCount := "select count(*) as count " + sqlExceptSelectCount

	totalRows := Count(queryCount, params...)
	records := []map[string]string{}

	data["rows"] = int64(0)
	data["pages"] = int64(0)
	data["list"] = records

	if totalRows == 0 {

		return data
	}
	if page == 0 {
		page = 1
	}
	if length == 0 {
		length = 20
	}
	offset := (page - 1) * length

	sql := query + " " + sqlExceptSelect + " limit " + strconv.FormatInt(length, 10) + " offset " + strconv.FormatInt(offset, 10)

	rows, err := dbmap.Query(sql, params...)
	defer rows.Close()

	if err != nil {

		fmt.Println("pgsql paginate error：%s,sql：%s,params：%+v",err.Error(),sql,params)
		return data
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))

	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record := make(map[string]string)
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col)
			}
		}
		records = append(records, record)
	}
	totalPages := int64(math.Floor(float64(totalRows)/float64(length)) + 1)
	if totalRows%length == 0 {
		totalPages = totalRows / length
	}
	data["rows"] = totalRows
	data["pages"] = totalPages
	data["list"] = records
	return data
}